/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package BD;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 *
 * @author Angelo
 */
public class MySqlCreador {

    private String user;
    private String pw;
    private final String dataBaseName = "mysql";
    private String host;
    private String url;
    private String nameNewDB;
    private LogsMySql log;
    public Connection conexion = null;

    public MySqlCreador(String user, String pw, String host, String nameNewDB) {
        log = new LogsMySql();

        this.user = user;
        this.pw = pw;
        this.host = host;
        url = "jdbc:mysql://" + this.host + "/" + this.dataBaseName;

        this.nameNewDB = nameNewDB;
    }

    private void ModificarConfiguracion() {
        ConfiguracionMySql cfg2;
        CargarConfig cargarCfg = new CargarConfig();
        cfg2 = cargarCfg.LeerArchivo();
        cfg2.setDataBaseName(nameNewDB);
        cargarCfg.CrearArchivo(cfg2);
    }

    public int CrearBD() {
        int res;
        try {
            String sql = "CREATE DATABASE " + nameNewDB + ";";
            PreparedStatement ps = conexion.prepareStatement(sql);

            res = ps.executeUpdate(sql);
            this.ModificarConfiguracion();
            log.InsertarLog("Base de datos creada");
        } catch (SQLException | NullPointerException ex) {

            if (ex.toString().contains("database exists")) {
                log.InsertarLog("La base de datos ya exite: " + ex);
                res = -1;
            } else {
                log.InsertarLog("Error al crear base de datos: " + ex);
                res = 0;
            }
        }

        return res;
    }

    public int CrearTablas() {
        int res;
        try {
            String sql = "USE " + nameNewDB;
            PreparedStatement ps = conexion.prepareStatement(sql);
            ps.execute(sql);
            sql = " CREATE TABLE `Responsable` (\n"
                    + "  `Id_Responsable` int NOT NULL auto_increment, \n"
                    + "  `Nombre` varchar(100) not NULL,\n"
                    + "`Apodo` varchar(45) not NULL,\n"
                    + "`Email` varchar(150) not NULL UNIQUE,\n"
                    + "`PathFoto` varchar(256) not NULL,\n"
                    + "  PRIMARY KEY  (`Id_Responsable`)\n"
                    + ")  ENGINE = INNODB";
            ps = conexion.prepareStatement(sql);
            ps.execute(sql);
            log.InsertarLog("tabla Responsable creada");
            sql = "CREATE TABLE `Categoria` (\n"
                    + "  `Id_Categoria` int NOT NULL auto_increment, \n"
                    + "`Nombre` varchar(45) not NULL,\n"
                    + "`Descripcion` varchar(45) not NULL,\n"
                    + "  PRIMARY KEY  (`Id_Categoria`)\n"
                    + ") ENGINE = INNODB";
            ps = conexion.prepareStatement(sql);
            ps.execute(sql);
            log.InsertarLog("tabla Categoria creada");
            sql = "CREATE TABLE `Tarea` (\n"
                    + "  `Id_Tarea` int NOT NULL auto_increment, \n"
                    + "`Titulo` varchar(4000) not NULL,\n"
                    + "`Texto` varchar(500) not NULL,\n"
                    + "`Prioridad` int not NULL,\n"
                    + "`Vencimiento` DATETIME not NULL,\n"
                    + "`Completado` float not NULL,\n"
                    + "`Id_Responsable` int not NULL,\n"
                    + "`Id_Categoria` int not NULL,\n"
                    + "  PRIMARY KEY  (`Id_Tarea`),\n"
                    + "  index (`Id_Responsable`),\n"
                    + "  index (`Id_Categoria`),\n"
                    + "  constraint FOREIGN KEY (`Id_Responsable`) REFERENCES Responsable(`Id_Responsable`)"
                    + "ON DELETE RESTRICT ON UPDATE CASCADE,\n"
                    + " constraint FOREIGN KEY (`Id_Categoria`) REFERENCES Categoria(`Id_Categoria`)"
                    + "ON DELETE RESTRICT ON UPDATE CASCADE\n"
                    + ")  ENGINE = INNODB";
            ps = conexion.prepareStatement(sql);
            ps.execute(sql);
            res = 1;
            log.InsertarLog("tabla Tarea creada");
            log.InsertarLog("Tablas creadas");

        } catch (SQLException ex) {
            if (ex.toString().contains("already exists")) {
                log.InsertarLog("La tabla ya existe: " + ex);
                res = -1;
            } else {
                log.InsertarLog("Error al crear tablas: " + ex);
                res = 0;
            }
        }
        return res;
    }

    public boolean Conectar() {
        boolean flag;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conexion = DriverManager.getConnection(url, user, pw);
            flag = true;
            log.InsertarLog("Conexion on");
        } catch (SQLException | ClassNotFoundException ex) {
            flag = false;
            log.InsertarLog("Error al conectar: " + ex);
        }
        return flag;
    }

    public boolean Desconectar() {
        boolean flag;
        try {
            conexion.close();
            flag = true;
            log.InsertarLog("Conexion terminada");
        } catch (SQLException ex) {
            flag = false;
            log.InsertarLog("Error al cerrar la conexion: " + ex);
        }
        return flag;
    }
}
